/**
 * @Copyright 2012, Founder Mobile Media Technology（Chongqing） Co., Ltd L.P. All
 *            rights reserved. The information contained herein is confidential
 *            and proprietary to Founder Mobile Media Technology（Chongqing） Co.,
 *            Ltd, and considered a trade secret as defined under civil and
 *            criminal statutes. Founder Mobile Media Technology（Chongqing） Co.,
 *            Ltd shall pursue its civil and criminal remedies in the event of
 *            unauthorized use or misappropriation of its trade secrets. Use of
 *            this information by anyone other than authorized employees of
 *            Founder Mobile Media Technology（Chongqing） Co., Ltd is granted
 *            only under a written non-disclosure agreement, expressly
 *            prescribing the scope and manner of such use.
 */
package cn.gxg.test.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


/**
 * 
 *
 * @createTime: 2012-12-6 下午04:30:28
 * @author: <a href="mailto:gaoxg@FOUNDER.COM.CN">Xuguo Gao</a>
 * @version: 0.1
 * @lastVersion: 0.1
 * @updateTime: 
 * @updateAuthor: <a href="mailto:gaoxg@FOUNDER.COM.CN">Xuguo Gao</a>
 * @changesSum: 
 * 
 */
public class InsertMobileNumber {
	private Connection sourceConn = null;
	private Connection targetConn = null;
	
	private void getConn() {
		String url = "jdbc:oracle:thin:@172.18.130.113:1521:bidbtest";
		String user = "cq_stat_user";
		String password = "cq_stat_user-1016";
		try {
			sourceConn = (Connection) DriverManager.getConnection(url,user,password);
			// 关闭事务自动提交
			sourceConn.setAutoCommit(false);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		url = "jdbc:oracle:thin:@172.18.130.113:1521:fzdbtest";
		user = "analy_tcode";
		password = "analy_tcode-1129";
		try {
			targetConn = (Connection) DriverManager.getConnection(url,user,password);
			targetConn.setAutoCommit(false);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public static void main(String[] args) {
		InsertMobileNumber imn = new InsertMobileNumber();
		imn.getConn();
		imn.insert();
	};
	private void insert(){
		try {
			long startTime = System.currentTimeMillis();
			
			String mobileNum = "13499230782,13497986742,13496742701,13495498661,13494254620,13493010579,13491766539,14798395665,13490522498,13492595899,13491351859,13490107818,13499645462,13498401422,13497157381,13495913341,13494669300,13493425260,13492181219,13490937179,13497235437,13440912311,13497235437,13440912311,13440912311,13497235437,14798395665,13498816102,13497572061,13496328021,13495083980,18871934730,18871934730,13490329612,13490329612,18871934730,13490329612,18897794715,18897794715,18897794715,13491026131,13491026131,15919866267,13495339851,13494713672,13495339851,13494047556,13494047556,13494713672,13494047556,13495339851,13447839337,13447839337,13494713672,13499295349,13437205196,13499295349,13499065781,13499065781,13499295349,13499065781,13493679500,13493679500,13493679500,13960016628,13960011025,13499019217,13448741778,13448741778,13960012032,13960014380,13499019217,13499019217,13448741778,13960015347,13441639019,13441639019,13960012079,13493308305,13960019998,13960016221,13493308305,13441639019,13960016628,13498828309,13437205196,13960019998,13498828309,13498828309,13491857828,18821524940,15919854045,13491857828,18838726498,15906856075,18821524940,18821524940,18838726498,18838726498,13491857828,13437205196,13443367896,13443367896,13443367896,13446196237,15906853079,15906856888,15789587598,15906858530,15906856408,13949218580,15919851626,13949211231,14798018757,13424674934,13960653521,13960016070,13960013240,13960656328,14797728959,14797720056,13949216252,15906856775,14798132384,13445445783,13445445783,14798132384,13949213700,13949213700,15919854045,13949213700,13949213700,13436333132,14798118382,14798118382,15919854045,14799777315,15919854045,15919878620,14799273067,14799276809,14799360167,14799263968,14799353541,14799263212,14799333228,14799280540,14799303775,14799299851,14799266603,14799306390,14799260317,14799269825,14799216108,14799300507,14799329379,15906857664,14798011207,13436339308,15919857864,14798187777,14798187777,13949216225,13960011025,13960014680,14797725728,13418441650,13437201494,13418448234,15920805171,13418448236,15900140184,15920801370,15919871064,15919869691,15919860668,15919850816,15919870102,15919864525,15919859675,13418447122,13418449627,15919855503,15919876662,13418448931,13436339087,15919855252,15919876307,15919865809,15919869517,15907041444,13418449730,13447836401,15920808591,13418449149,15919873854,13418449741,15919875336,13418449774,13418449694,13418448250,15920803399,15919854991,13418449103,13418441490,15920801900,13418448164,15920806997,13448090982,14798112333,14798019275,15746617899,14798019700,14798018757,14798010124,13949042327,15900145532,14797725180,13949211249,15900145532,15900145532,14797723119,14797723600,15906855918,14797723077,14797729905,14797726110,13436333730,13960011025,13436333730,15920796253,15920796253,15919867023,15919867023,13436336151,13436336151,15920816448,13436339000,15919855590,15906856500,15906856500,13443344334,15741571230,14798165846,13943655558,13943658565,14797727171,13943656777,15743836776,13436330184,15907076830,15900150881,15920819453,13437205811,13498877766,13498877766,13499887755,18878899810,13498887766,13498887766,14797723600,14798632541,13499887755,13498738061,13436330598,13498877766,13498887766,13498877766,13498877766,13498887766,13498887766,13499887755,13499887755,13498887766,13498877766,13498877766,13499887755,13498887766,15919854831,13498877766,13498877766,13498877766,13498887766,14798116584,13499260919,13498877766,13498887766,14797723600,13499887755,18878899810,13498877766,13498877766,13498877766,13498877766,13418440364,13436338909,18878845414,18878845414,18878845414,18878845414,18878841412,18878841412,18878841412,18878841412,18878841412,18878845414,18878841412,18878841412,18871355646,18878845414,18878845414,13436338909,18878845452,13418446789,15919874452,15919879988,13949227684,13960651078,15919879988,15919874452,15919858170,13436336033,14799285697,15920815271,13436330240,15919854045,13436330984,13496096369,13960017699,13960017699,15919854015,15919872610,18878841412,15919872610,18878845425,15785236952,15934251855,14725836912,15907048234,18878841412,13960016847,13436332802,13436331658,15906855951,18878841425,18878852345,13436339308,15906855899,15906856888,13436335104,15906858530,15906850813,15906857709,15789587598,15906859068,18878842301,15906853166,15906851562,13436338909,15919866047,15919876555,15919858963,15907076960,15907076960,15919870115,15919862627,14798178226,15907076960,15919873515,15919876502,15919879753,15919850595,15907076960,13437200222,13960012906,13960012906,15919857843,15919864528,13446488681,14799281063,13949229833,15919876192,13424823007,13436339922,13436339922,13436339922,13436339922,13436339922,13436339922,13436339922,13436339922,15919853119,13436339922,13436339922,13436339922,13436339922,13436339922,13436339922,13436339922,13436339922,13949046628,15919853119,13949046628,15919853119,13436339922,13436339922,13436339922,13436339922,13436339922,13436339922,13436339922,13436339922,13436339922,13436339922,13436339922,13436339922,13436339922,13436339922,15920814114,13437204035,13437205265,13437204523,13437201600,13437206624,13437208036,13437206074,13437202873,13437204391,13437202873,13437201333,13437201782,13437204523,13437204741,13437202258,13437206071,13437206624,13437204741,13437205265,13437206074,13437200342,13437206071,13437204391,13437201782,13436334923,13436334923,13436334923,13436334923,13436334923,13436334923,13436334923,13436334923,13436334923,13436334923,13436334923,13436334923,13436334923,13436334923,15906850788,13949224219,13436334923,14798185294,13436333699,15919860815,15919860815,14797737337,15919864528,13424823007,13960657242,15920810514,13949047053,13949047053,13949043376,13436332802,15906853079,13436330984,15906853181,15906853175,14797723917,15919850228,14797726119,13949213700,15786788754,14798013531,15919856163,15919869869,15919866024,15919850544,13949042355,15919850228,15919853665,15919878858,15919871898,14798011847,15919865452,15919876050,15919855221,15919859436,15907043169,15900140877,15919859436,13949040004,13960013649,13960015629,13960015629,14797974929,15920818113,13960010530,13960013200,14797711860,14797711960,13960013074,13960012361,13960013606,13960018169,13960012072,15920815552,13445112548,13960012892,13960016191,13960017806,13960013910,13960014810,13436333730,13960014815,13960017956,13960011668,13960018996,13960011777,13960017828,13424823868,13960016009,13960016567,13960018966,13960012239,13960017956,13960011768,13960018969,15906857748,13960019200,13960018969,13960011668,13960019915,13960014815,13960017828,15919874520,13949218905,15919868867,15919854028,13418441720,13960012668,13960017878,13960017878,15919857501,13960012668,13960017757,13960012668,15907078502,15900162587,15919879555,15900162587,15906857748,15919876502,15907074008,13436332020,13960018157,13424820120,13960011498,13960019498,15907076888,13960012688,13960015994,13960018105,13960015994,13960010519,13960016195,13424672390,13424820120,13960016195,13960019390,14798209876,13424820120,14798209876,13418440317,13424671560,13960010519,13960019498,13960018105,15919854363,15919854666,13960013380,15919876987,13949042226,13960011753,13960016855,13960013380,13960016855,15920810929,15919851690,15919853290,13960014672,14797711860,13949042226,13960015385,13960016637,13960012249,14797711230,15919852999,13960019929,15906858080,15920803882,14799339816,15919879988,13960014481,13492342172,13960015335,13960016758,15919872157,13960016758,15920812920,13949215617,13960012021,15919863275,13960018800,13960013649,14798004066,13960018653,14797728187,13436335241,13960014738,13960013036,13960013498,13960018628,13960012598,13960011723,13960011623,13960018619,13424822479,15919861167,13960010922,13960012598,15919878928,13960013649,15920819568,13949041314,13960014522,14797722668,13960013649,13960010056,13960018985,15906850595,15919866115,15906858530,13960011062,13960015369,13960013922,13960018861,13960013816,13960012001,13960014680,13960012930,15906850813,15906853166,15906857709,15906855899,13960011740,13960013920,15906850243,15789587598,13960011621,13960015722,15919861167,13960015213,15906856888,13960010003,15900162587,15900162587,13949214159,13960016151,13960012995,15919864670,13949046009,13424820120,13424820120,13960011626,13960013420,13424820120,15919875987,15920811040,13960012446,13960015022,13424820120,13949046009,13436335579,15920819744,13960019111,13960017938,13418449208,13445456565,13960017971,13960015069,13960016623,13960010371,13960013856,13960013539,13418443090,15919871828,13960013768,15919868867,13436331919,13436332668,13960017120,13960017864,13960016568,13960012801,13960016878,13960016879,13960012784,13960019688,13960016966,13960012901,13418443090,13960012295,13960012384,13418449208,13960011075,13960017732,13960010349,15919858223,13960011579,13960018269,13960012786,13960016955,15919871180,13960013701,13960012116,13960016681,13960012685,15919867023,13949042909";
	
			Statement stm=sourceConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
			String querySql = "select t.provincename,t.cityname from dm_num_segment t where '&num&' between t.beginno and t.endno";
			String insertSql = "insert into T_DATA_MOBILE " +
					"(MOBILENUMBER,MOBILEAREA,PROVINCE,CITY)" +
					" values (?,?,?,?)";
			String[] strs = mobileNum.split(",");
			PreparedStatement pst = (PreparedStatement) targetConn.prepareStatement(insertSql);
			ResultSet rs = null;
			int i = 0;
			System.out.println("开始插入数据！");
			String tempSql = "";
			StringBuffer emptyStr = new StringBuffer();
			for(String num : strs) {
				tempSql = querySql.replace("&num&", num);
				rs =stm.executeQuery(tempSql);
				rs.last(); //移到最后一行  
				if(rs.getRow()<1) {
					emptyStr.append(num);
					continue;
				}
				rs.beforeFirst();
				while(rs.next()) {
					pst.setString(1, num.substring(0, 7));
					pst.setString(2, rs.getString(1) +" " + rs.getString(2));
					pst.setString(3, rs.getString(1));
					pst.setString(4, rs.getString(2));
				}
				pst.addBatch();
				
				if(i==100) {
					// 执行批量更新
					pst.executeBatch();
					// 语句执行完毕，提交本事务
					targetConn.commit();
					i=0;
					System.out.println("提交数据！");
				}
				i++;
			}
			if(i>0){
				// 执行批量更新
				pst.executeBatch();
				// 语句执行完毕，提交本事务
				targetConn.commit();
			}
			if(null != rs)rs.close();
			stm.close();
			sourceConn.close();
			
			pst.close();
			targetConn.close();
			long endTime = System.currentTimeMillis();
			System.out.println("插入数据结束，用时：" + (endTime - startTime));
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}
